-- **************************************************************************
--    Project Name:   支干线运输路段宽表-明细
--    Job Name:       jms_dwd.dwd_wide_tms_road_waybill_hf
--    Description :   支干线运输路段宽表明细
--    Author :        荣光凡
--    date：          2022/05/09
-- **************************************************************************
--
-- **************************************************************************
--    modify by 荣光凡 2022/05/09 新增脚本
--    modify by 荣光凡 2022/05/15 新增小时调度，关联运单表切为关联订单表
-- **************************************************************************
--    Relation:
--    jms_dwd.dwd_wide_tms_road_waybill_hf << [
--         jms_dwd__dwd_tmsnew_shipment_union_base_hi,
--         jms_dwd__dwd_tmsnew_shipment_stop_union_base_hi,
--         jms_dim__dim_tmsnew_vehicle,
--         jms_dim__dim_tmsnew_vehicle_line,
--         jms_dwd__dwd_tab_barscan_other_base_hi,
--         jms_dwd__dwd_tab_barscan_loading_base_hi,
--         jms_dwd__dwd_tab_barscan_unloading_base_hi,
--         jms_dwd__dwd_tab_barscan_centersend_base_hi,
--         jms_dwd__dwd_tab_barscan_centerarrival_base_hi,
--         jms_dwd__dwd_tab_barscan_sitesend_base_hi,
--         jms_dwd__dwd_tab_barscan_sitearrival_base_hi,
--         jms_dwd__dwd_yl_oms_oms_order_hf
--    ]
-- **************************************************************************

-- 支干线任务数据
with tab_tms_branch_shipment as (
    select
        id as shipment_id,shipment_no,shipment_name,start_code,start_name,end_code,end_name
        ,cnt as shipment_site_cnt,vehicleline_id,vehicleline_code,vehicleline_name,shifts
        ,business_attribute,operation_model,shipment_type,planned_departure_time,planned_arrival_time
        ,actual_departure_time,actual_arrival_time,total_runtime,total_intransittime,span_days
        ,standard_speed,mileage,plate_number,vehicletype_id,vehicletype_name,trailer_number,carrier_id,carrier_type
        ,carrier_name,vehicle_door_cnt,driver_name,driver_contact,quotation_model,billing_way,settlement_vehicle_type_id
        ,settlement_vehicle_type_name,freight_generate_status,overtime_type,shipment_state,is_ballast,table_type as is_trunk_branch
    from jms_dwd.dwd_tmsnew_shipment_union_base_hi
    where dt >= '{{ execution_date | date_add(-7) | cst_hour }}'
    and dt <= '{{ execution_date | cst_hour }}'
),
-- 支干线任务号与车辆表，车线表关联
tab_tms_branch_shipment_line_car as (
    select
    shipment.*
    ,car.load_weight
    ,car.vehicle_volume
    ,car.nuclear_load_num
    ,line.is_stop    --是否经停
    ,line.is_assist_line    --是否辅线
    ,line.vehicleline_type    --线路类型 1总部招标；2加盟商供车；3临时车线；4网点自跑
    ,line.has_out -- 是否兜底1-是2-否
    from tab_tms_branch_shipment shipment
    left join jms_dim.dim_tmsnew_vehicle_line line
    on shipment.vehicleline_id = line.id
    left join jms_dim.dim_tmsnew_vehicle car
    on shipment.plate_number = car.plate_number
),
-- 合并支干线经停数据
tab_tms_branch_stop as (
    select
        shipment_id,shipment_no,sort_num,network_id,network_code,network_name,roadsection_id,planned_arrival_day,planned_arrival_hour
        ,planned_arrival_time,actual_arrival_time,planned_departure_day,planned_departure_hour,planned_departure_time,actual_departure_time
        ,stop_time,usetime,mileage,speed,batch_time,handling_type,load_start_time,load_end_time,unload_start_time,unload_end_time
        ,app_arrival_time,app_departure_time,gps_arrival_time,gps_departure_time,lock_time,unlock_time,weight_arrival_time
        ,weight_departure_time,arrival_gross_weight,arrival_tare_weight,arrival_net_weight,departure_gross_weight,departure_tare_weight
        ,departure_net_weight,estimate_arrival_time,plan_driving_time,actual_driving_time,actual_arrival_time_source
        ,actual_departure_time_source,estimate_batch_time,actual_batch_time,create_by,create_by_name,create_time,update_by
        ,update_by_name,update_time,simple_name,latest_arrival_time,gps_depart_mileage,gps_arrival_mileage,is_send,dt
    from jms_dwd.dwd_tmsnew_shipment_stop_union_base_hi
    where dt >= '{{ execution_date | date_add(-7) | cst_hour }}'
    and dt <= '{{ execution_date | cst_hour }}'
),
-- 支干线经停表自关联 获得路段数据
stop_start_to_end as (
    select
         ----------- 路段 -----------
         start_site.shipment_no                                       -- 任务号
         ,start_site.sort_num as road_section_id                       -- 路段编号
         ,start_site.usetime                                           -- 运输耗时(MIN)
         ,start_site.mileage                                           -- 规划里程(KM)
         ,start_site.speed                                             -- 时速(KM/H)
         ,start_site.batch_time                                          -- 批次时间
         ,start_site.plan_driving_time                                  -- 规定行驶时间
         ,start_site.actual_driving_time                               -- 实际行驶时间
         ,start_site.estimate_batch_time                               -- 应赶批次
         ,start_site.actual_batch_time                                  -- 实赶批次时间
         ,start_site.handling_type                                     -- 装卸类型
         ----------- 始发 -----------
         ,start_site.sort_num as start_site_sort_num                   -- 始发站点顺序编号
         ,start_site.network_code as start_site_code                      -- 始发站点编号
         ,start_site.network_name as start_site_name                      -- 始发站点名称
         ,start_site.load_start_time as load_start_time                -- 开始装货
         ,start_site.load_end_time as load_end_time                    -- 装货完成
         ,start_site.lock_time                                         -- 锁车时间
         ,start_site.app_departure_time                                -- APP离开时间
         ,start_site.gps_departure_time                                -- GPS离开时间
         ,start_site.weight_departure_time                             -- 过磅出站时间
         ,start_site.planned_departure_time                            -- 计划离开时间
         ,start_site.actual_departure_time                             -- 实际离开时间
         ,start_site.departure_gross_weight                            -- 离开毛重
         ,start_site.departure_tare_weight                             -- 离开皮重
         ,start_site.departure_net_weight                              -- 离开净重
         ,start_site.gps_depart_mileage                                -- GPS发车里程
         ----------- 目的 -----------
         ,end_site.sort_num as end_site_sort_num                     -- 目的站点顺序编号
         ,end_site.network_code as end_site_code                      -- 目的站点编号
         ,end_site.network_name as end_site_name                      -- 目的站点名称
         ,end_site.unload_start_time                                 -- 开始卸货
         ,end_site.unload_end_time                                   -- 卸货完成
         ,end_site.unlock_time                                          -- 解锁时间
         ,end_site.app_arrival_time                                  -- APP到达时间
         ,end_site.gps_arrival_time                                  -- GPS到达时间
         ,end_site.weight_arrival_time                               -- 过磅进站时间
         ,end_site.planned_arrival_time                              -- 计划到达时间
         ,end_site.actual_arrival_time                               -- 实际到达时间
         ,end_site.arrival_gross_weight                              -- 到达毛重
         ,end_site.arrival_tare_weight                               -- 到达皮重
         ,end_site.arrival_net_weight                                -- 到达净重
         ,end_site.gps_arrival_mileage                               -- GPS到车里程
         ,end_site.latest_arrival_time                               -- 最晚到车时间
         ,end_site.estimate_arrival_time                             -- 预计到达时间=上一站实际发车时间+本站运输耗时(MIN)
    from tab_tms_branch_stop start_site
    inner join tab_tms_branch_stop end_site
    on start_site.shipment_no = end_site.shipment_no
    and start_site.sort_num = end_site.sort_num - 1
),
-- 支干线任务表与支干线经停表关联
tab_tms_branch_shipment_stop as (
    select
        shipment.shipment_id
        ,shipment.shipment_no
        ,shipment.shipment_name
        ,shipment.start_code
        ,shipment.start_name
        ,shipment.end_code
        ,shipment.end_name
        ,shipment.shipment_site_cnt
        ,shipment.vehicleline_id
        ,shipment.vehicleline_code
        ,shipment.vehicleline_name
        ,shipment.shifts
        ,shipment.business_attribute
        ,shipment.operation_model
        ,shipment.shipment_type
        ,shipment.planned_departure_time as shipno_planned_departure_time
        ,shipment.planned_arrival_time as shipno_planned_arrival_time
        ,shipment.actual_departure_time as shipno_actual_departure_time
        ,shipment.actual_arrival_time as shipno_actual_arrival_time
        ,shipment.total_runtime
        ,shipment.total_intransittime
        ,shipment.span_days
        ,shipment.standard_speed
        ,shipment.mileage
    ---------经停-----------
         ----------- 路段 -----------
         ,site_stop.road_section_id                                   -- 路段编号
         ,site_stop.usetime                                           -- 运输耗时(MIN)
         ,site_stop.mileage as road_mileage                           -- 规划里程(KM)
         ,site_stop.speed                                             -- 时速(KM/H)
         ,site_stop.batch_time                                        -- 批次时间
         ,site_stop.plan_driving_time                                 -- 规定行驶时间
         ,site_stop.actual_driving_time                               -- 实际行驶时间
         ,site_stop.estimate_batch_time                               -- 应赶批次
         ,site_stop.actual_batch_time                                 -- 实赶批次时间
         ,site_stop.handling_type                                     -- 装卸类型
         ----------- 始发 -----------
         ,site_stop.start_site_sort_num                               -- 始发站点顺序编号
         ,site_stop.start_site_code                                   -- 始发站点编号
         ,site_stop.start_site_name                                   -- 始发站点名称
         ,site_stop.load_start_time                                   -- 开始装货
         ,site_stop.load_end_time                                     -- 装货完成
         ,site_stop.lock_time                                         -- 锁车时间
         ,site_stop.app_departure_time                                -- APP离开时间
         ,site_stop.gps_departure_time                                -- GPS离开时间
         ,site_stop.weight_departure_time                             -- 过磅出站时间
         ,site_stop.planned_departure_time                            -- 计划离开时间
         ,site_stop.actual_departure_time                             -- 实际离开时间
         ,site_stop.departure_gross_weight                            -- 离开毛重
         ,site_stop.departure_tare_weight                             -- 离开皮重
         ,site_stop.departure_net_weight                              -- 离开净重
         ,site_stop.gps_depart_mileage                                -- GPS发车里程
         ----------- 目的 -----------
         ,site_stop.end_site_sort_num                                 -- 目的站点顺序编号
         ,site_stop.end_site_code                                     -- 目的站点编号
         ,site_stop.end_site_name                                     -- 目的站点名称
         ,site_stop.unload_start_time                                 -- 开始卸货
         ,site_stop.unload_end_time                                   -- 卸货完成
         ,site_stop.unlock_time                                       -- 解锁时间
         ,site_stop.app_arrival_time                                  -- APP到达时间
         ,site_stop.gps_arrival_time                                  -- GPS到达时间
         ,site_stop.weight_arrival_time                               -- 过磅进站时间
         ,site_stop.planned_arrival_time                              -- 计划到达时间
         ,site_stop.actual_arrival_time                               -- 实际到达时间
         ,site_stop.arrival_gross_weight                              -- 到达毛重
         ,site_stop.arrival_tare_weight                               -- 到达皮重
         ,site_stop.arrival_net_weight                                -- 到达净重
         ,site_stop.gps_arrival_mileage                               -- GPS到车里程
         ,site_stop.latest_arrival_time                               -- 最晚到车时间
         ,site_stop.estimate_arrival_time                             -- 预计到达时间=上一站实际发车时间+本站运输耗时(MIN)
    ---------经停-----------
        ,shipment.plate_number    --    车牌号
        ,shipment.vehicletype_id    --    车型ID
        ,shipment.vehicletype_name    --    车型名称
        ,shipment.trailer_number    --    挂车号
        ,shipment.carrier_id    --    承运商ID
        ,shipment.carrier_type    --    承运商类型
        ,shipment.carrier_name    --    承运商全名
        ,shipment.driver_name    --    司机名称
        ,shipment.driver_contact    --    随车电话
        ,shipment.quotation_model    --    报价模式
        ,shipment.billing_way    --    计费方式（1：整车计重、2：零担计重、3：按趟计费）
        ,shipment.settlement_vehicle_type_id    --    结算车型ID
        ,shipment.settlement_vehicle_type_name    --    结算车型名称
        ,shipment.freight_generate_status    --    运费流水生成状态：1
        ,shipment.overtime_type    --    加班类型：11爆仓加班、12顶班、13应急加班
        ,shipment.shipment_state    --    任务状态:待调度0
        ,shipment.is_ballast    --    是否空放:1是
        ,shipment.is_trunk_branch
        ,shipment.load_weight
        ,shipment.vehicle_volume
        ,shipment.nuclear_load_num
        ,shipment.is_stop    --是否经停
        ,shipment.is_assist_line    --是否辅线
        ,shipment.vehicleline_type    --线路类型 1总部招标；2加盟商供车；3临时车线；4网点自跑
        ,shipment.has_out -- 是否兜底1-是2-否
    from tab_tms_branch_shipment_line_car shipment
    left join stop_start_to_end site_stop
    on shipment.shipment_no = site_stop.shipment_no
),
-- 封发车数据
other_send as (
  select * from (
    select
        waybill_no as car_code
        ,shipment_no
        ,scan_site_code
        ,scan_time
        ,row_number() over(partition by shipment_no,scan_site_code order by scan_time desc) as id
    from jms_dwd.dwd_tab_barscan_other_base_hi
    where dt >= concat('{{ execution_date | date_add(-8) | cst_ds }}','-00')
    and scan_type in ('封发车扫描')
  ) tmp
  where id = 1
),
-- 到解车数据
other_arrival as (
  select * from (
    select
        waybill_no as car_code
        ,shipment_no
        ,scan_site_code
        ,scan_time
        ,row_number() over(partition by shipment_no,scan_site_code order by scan_time asc) as id
    from jms_dwd.dwd_tab_barscan_other_base_hi
    where dt >= concat('{{ execution_date | date_add(-8) | cst_ds }}','-00')
    and scan_type in ('到解车扫描')
  ) tmp
  where id = 1
),
-- 装车表剔除经停线路之外的扫描
loading as (
    select
        loading.shipment_no,loading.waybill_no,loading.package_code,loading.scan_site,loading.scan_site_code
        ,loading.next_station,loading.next_station_code,loading.scan_time
    from (
        select
            shipment_no,waybill_no,package_code,scan_site,scan_site_code
            ,next_station,next_station_code,scan_time
        from jms_dwd.dwd_tab_barscan_loading_base_hi
        where dt >= concat('{{ execution_date | date_add(-8) | cst_ds }}','-00')
        and shipment_no is not null and scan_site_code is not null --and waybill_no like 'JT%'
    ) loading
    inner join tab_tms_branch_shipment_stop road
    on loading.shipment_no = road.shipment_no
    and loading.scan_site_code = road.start_site_code
    and loading.next_station_code = road.end_site_code
),
-- 合并装车，中心发件，网点发件数据
tab_loading_send as (
    select
    shipment_no,waybill_no
    ,max(package_code) as package_code
    ,scan_site_code
    ,max(load_next_station) as load_next_station
    ,max(load_next_station_code) as load_next_station_code
    ,max(load_scan_time) as load_scan_time
    ,max(send_next_station) as send_next_station
    ,max(send_next_station_code) as send_next_station_code
    ,max(send_scan_time) as send_scan_time
    from (
        -- 装车表
        select
            shipment_no,waybill_no,package_code,'loading' as scan_type,scan_site,scan_site_code
            ,next_station as load_next_station,next_station_code as load_next_station_code,scan_time as load_scan_time
            ,null  send_next_station,null  send_next_station_code,null  send_scan_time
            ,row_number() over(partition by shipment_no,waybill_no,scan_site_code order by scan_time desc) as row_id
        from loading
        union all
        -- 中心发件表
        select
            transfer_code as shipment_no,waybill_no,package_code,'center_send' as scan_type,scan_site,scan_site_code
            ,null  load_next_station,null  load_next_station_code,null  load_scan_time
            ,next_station as send_next_station,next_station_code as send_next_station_code,scan_time as send_scan_time
            ,row_number() over(partition by transfer_code,waybill_no,scan_site_code order by scan_time desc) as row_id
        from jms_dwd.dwd_tab_barscan_centersend_base_hi
        where dt >= concat('{{ execution_date | date_add(-7) | cst_ds }}','-00')
        and transfer_code is not null and scan_site_code is not null-- and waybill_no like 'JT%'
        union all
        -- 网点发件表
        select
            shipment_no,waybill_no,package_code,'site_cend' as scan_type,scan_site,scan_site_code
            ,null  load_next_station,null  load_next_station_code,null  load_scan_time
            ,next_station as send_next_station,next_station_code as send_next_station_code,scan_time as send_scan_time
            ,row_number() over(partition by shipment_no,waybill_no,scan_site_code order by scan_time desc) as row_id
        from jms_dwd.dwd_tab_barscan_sitesend_base_hi
        where dt >= concat('{{ execution_date | date_add(-7) | cst_ds }}','-00')
        and shipment_no is not null and scan_site_code is not null-- and waybill_no like 'JT%'
    ) union_tmp
    where row_id = 1
    group by
    shipment_no,waybill_no,scan_site_code
),
-- 合并卸车，中心到件，网点到件数据
tab_unloading_arrival as (
    select
    shipment_no,waybill_no
    ,max(package_code) as package_code
    ,scan_site_code
    ,max(unload_pre_station) as unload_pre_station
    ,max(unload_pre_station_code) as unload_pre_station_code
    ,max(unload_scan_time) as unload_scan_time
    ,max(arrival_pre_station) as arrival_pre_station
    ,max(arrival_pre_station_code) as arrival_pre_station_code
    ,max(arrival_scan_time) as arrival_scan_time
    from (
        -- 卸车表
        select
            shipment_no,waybill_no,package_code,'unloading' as scan_type,scan_site,scan_site_code
            ,pre_station as unload_pre_station,pre_station_code as unload_pre_station_code,scan_time as unload_scan_time
            ,null  arrival_pre_station,null  arrival_pre_station_code,null  arrival_scan_time
            ,row_number() over(partition by shipment_no,waybill_no,scan_site_code order by scan_time desc) as row_id
        from jms_dwd.dwd_tab_barscan_unloading_base_hi
        where dt >= concat('{{ execution_date | date_add(-7) | cst_ds }}','-00')
        and shipment_no is not null and scan_site_code is not null-- and waybill_no like 'JT%'
        union all
        -- 中心到件表
        select
            transfer_code as shipment_no,package_code,waybill_no,'center_arrival' as scan_type,scan_site,scan_site_code
            ,null  unload_pre_station,null  unload_pre_station_code,null  unload_scan_time
            ,pre_station as arrival_pre_station,pre_station_code as arrival_pre_station_code,scan_time as arrival_scan_time
            ,row_number() over(partition by transfer_code,waybill_no,scan_site_code order by scan_time desc) as row_id
        from jms_dwd.dwd_tab_barscan_centerarrival_base_hi
        where dt >= concat('{{ execution_date | date_add(-7) | cst_ds }}','-00')
        and transfer_code is not null and scan_site_code is not null-- and waybill_no like 'JT%'
        union all
        -- 网点到件表
        select
            shipment_no,waybill_no,package_code,'site_cend' as scan_type,scan_site,scan_site_code
            ,null  unload_pre_station,null  unload_pre_station_code,null  unload_scan_time
            ,pre_station as arrival_pre_station,pre_station_code as arrival_pre_station_code,scan_time as arrival_scan_time
            ,row_number() over(partition by shipment_no,waybill_no,scan_site_code order by scan_time desc) as row_id
        from jms_dwd.dwd_tab_barscan_sitearrival_base_hi
        where dt >= concat('{{ execution_date | date_add(-7) | cst_ds }}','-00')
        and shipment_no is not null and scan_site_code is not null-- and waybill_no like 'JT%'
    ) union_tmp
    where row_id = 1
    group by
    shipment_no,waybill_no,scan_site_code
),
-- 路段维度始发站点数据
start_site as (
    select
        shipment_stop.shipment_id
        ,shipment_stop.shipment_no
        ,shipment_stop.shipment_name
        ,loading_send.waybill_no
        ,loading_send.package_code
        ,shipment_stop.start_code
        ,shipment_stop.start_name
        ,shipment_stop.end_code
        ,shipment_stop.end_name
        ,shipment_stop.shipment_site_cnt
        ,shipment_stop.vehicleline_id
        ,shipment_stop.vehicleline_code
        ,shipment_stop.vehicleline_name
        ,shipment_stop.shifts
        ,shipment_stop.business_attribute
        ,shipment_stop.operation_model
        ,shipment_stop.shipment_type
        ,shipment_stop.shipno_planned_departure_time
        ,shipment_stop.shipno_planned_arrival_time
        ,shipment_stop.shipno_actual_departure_time
        ,shipment_stop.shipno_actual_arrival_time
        ,shipment_stop.total_runtime
        ,shipment_stop.total_intransittime
        ,shipment_stop.span_days
        ,shipment_stop.standard_speed
        ,shipment_stop.mileage
    ---------经停-----------
         ----------- 路段 -----------
         ,shipment_stop.road_section_id                       -- 路段编号
         ,shipment_stop.usetime                                           -- 运输耗时(MIN)
         ,shipment_stop.road_mileage                                            -- 规划里程(KM)
         ,shipment_stop.speed                                             -- 时速(KM/H)
         ,shipment_stop.batch_time                                          -- 批次时间
         ,shipment_stop.plan_driving_time                                  -- 规定行驶时间
         ,shipment_stop.actual_driving_time                               -- 实际行驶时间
         ,shipment_stop.estimate_batch_time                               -- 应赶批次
         ,shipment_stop.actual_batch_time                                  -- 实赶批次时间
         ,shipment_stop.handling_type                                     -- 装卸类型
         ----------- 始发 -----------
         ,shipment_stop.start_site_sort_num                   -- 始发站点顺序编号
         ,shipment_stop.start_site_code                      -- 始发站点编号
         ,shipment_stop.start_site_name                      -- 始发站点名称
         ,shipment_stop.load_start_time                -- 开始装货
         ,shipment_stop.load_end_time                    -- 装货完成
         ,shipment_stop.lock_time                                         -- 锁车时间
         ,loading_send.load_scan_time                                     -- 装车时间
         ,loading_send.load_next_station_code                             -- 装车下一站编码
         ,loading_send.load_next_station as load_next_station_name        -- 装车下一站名称
         ,other_send.scan_time as send_car_scan_time                      -- 封发车时间
         ,other_send.car_code as send_car_code                            -- 封发车号
         ,shipment_stop.app_departure_time                                -- APP离开时间
         ,shipment_stop.gps_departure_time                                -- GPS离开时间
         ,shipment_stop.weight_departure_time                             -- 过磅出站时间
         ,shipment_stop.planned_departure_time                            -- 计划离开时间
         ,shipment_stop.actual_departure_time                             -- 实际离开时间
         ,shipment_stop.departure_gross_weight                            -- 离开毛重
         ,shipment_stop.departure_tare_weight                             -- 离开皮重
         ,shipment_stop.departure_net_weight                              -- 离开净重
         ,shipment_stop.gps_depart_mileage                                -- GPS发车里程
         ,loading_send.send_scan_time                                     -- 发件时间
         ,loading_send.send_next_station_code                             -- 发件下一站编码
         ,loading_send.send_next_station as send_next_station_name        -- 发件下一站名称
         ----------- 目的 -----------
         ,shipment_stop.end_site_sort_num                     -- 目的站点顺序编号
         ,shipment_stop.end_site_code                      -- 目的站点编号
         ,shipment_stop.end_site_name                      -- 目的站点名称
         ,shipment_stop.unload_start_time                                 -- 开始卸货
         ,shipment_stop.unload_end_time                                   -- 卸货完成
         ,shipment_stop.unlock_time                                          -- 解锁时间
         ,shipment_stop.app_arrival_time                                  -- APP到达时间
         ,shipment_stop.gps_arrival_time                                  -- GPS到达时间
         ,shipment_stop.weight_arrival_time                               -- 过磅进站时间
         ,shipment_stop.planned_arrival_time                              -- 计划到达时间
         ,shipment_stop.actual_arrival_time                               -- 实际到达时间
         ,shipment_stop.arrival_gross_weight                              -- 到达毛重
         ,shipment_stop.arrival_tare_weight                               -- 到达皮重
         ,shipment_stop.arrival_net_weight                                -- 到达净重
         ,shipment_stop.gps_arrival_mileage                               -- GPS到车里程
         ,shipment_stop.latest_arrival_time                               -- 最晚到车时间
         ,shipment_stop.estimate_arrival_time                             -- 预计到达时间=上一站实际发车时间+本站运输耗时(MIN)
    ---------经停-----------
        ,shipment_stop.plate_number    --    车牌号
        ,shipment_stop.vehicletype_id    --    车型ID
        ,shipment_stop.vehicletype_name    --    车型名称
        ,shipment_stop.trailer_number    --    挂车号
        ,shipment_stop.carrier_id    --    承运商ID
        ,shipment_stop.carrier_type    --    承运商类型
        ,shipment_stop.carrier_name    --    承运商全名
        ,shipment_stop.driver_name    --    司机名称
        ,shipment_stop.driver_contact    --    随车电话
        ,shipment_stop.quotation_model    --    报价模式
        ,shipment_stop.billing_way    --    计费方式（1：整车计重、2：零担计重、3：按趟计费）
        ,shipment_stop.settlement_vehicle_type_id    --    结算车型ID
        ,shipment_stop.settlement_vehicle_type_name    --    结算车型名称
        ,shipment_stop.freight_generate_status    --    运费流水生成状态：1
        ,shipment_stop.overtime_type    --    加班类型：11爆仓加班、12顶班、13应急加班
        ,shipment_stop.shipment_state    --    任务状态:待调度0
        ,shipment_stop.is_ballast    --    是否空放:1是
        ,shipment_stop.is_trunk_branch
        ,shipment_stop.load_weight
        ,shipment_stop.vehicle_volume
        ,shipment_stop.nuclear_load_num
        ,shipment_stop.is_stop    --是否经停
        ,shipment_stop.is_assist_line    --是否辅线
        ,shipment_stop.vehicleline_type    --线路类型 1总部招标；2加盟商供车；3临时车线；4网点自跑
        ,shipment_stop.has_out -- 是否兜底1-是2-否
        ,concat(substr(shipment_stop.shipno_planned_departure_time,1,10),'-',substr(shipment_stop.shipno_planned_departure_time,12,2)) as dt
    from tab_tms_branch_shipment_stop shipment_stop
    left join other_send other_send
    on shipment_stop.shipment_no = other_send.shipment_no
    and shipment_stop.start_site_code = other_send.scan_site_code
    left join tab_loading_send loading_send
    on shipment_stop.shipment_no = loading_send.shipment_no
    and shipment_stop.start_site_code = loading_send.scan_site_code
    -- where other_send.id = 1
),
-- 路段维度目的站点数据
end_site as (
    select
        shipment_stop.shipment_id
        ,shipment_stop.shipment_no
        ,shipment_stop.shipment_name
        ,unloading_arrival.waybill_no
        ,unloading_arrival.package_code
        ,shipment_stop.start_code
        ,shipment_stop.start_name
        ,shipment_stop.end_code
        ,shipment_stop.end_name
        ,shipment_stop.shipment_site_cnt
        ,shipment_stop.vehicleline_id
        ,shipment_stop.vehicleline_code
        ,shipment_stop.vehicleline_name
        ,shipment_stop.shifts
        ,shipment_stop.business_attribute
        ,shipment_stop.operation_model
        ,shipment_stop.shipment_type
        ,shipment_stop.shipno_planned_departure_time
        ,shipment_stop.shipno_planned_arrival_time
        ,shipment_stop.shipno_actual_departure_time
        ,shipment_stop.shipno_actual_arrival_time
        ,shipment_stop.total_runtime
        ,shipment_stop.total_intransittime
        ,shipment_stop.span_days
        ,shipment_stop.standard_speed
        ,shipment_stop.mileage
    ---------经停-----------
         ----------- 路段 -----------
         ,shipment_stop.road_section_id                       -- 路段编号
         ,shipment_stop.usetime                                           -- 运输耗时(MIN)
         ,shipment_stop.road_mileage                                            -- 规划里程(KM)
         ,shipment_stop.speed                                             -- 时速(KM/H)
         ,shipment_stop.batch_time                                          -- 批次时间
         ,shipment_stop.plan_driving_time                                  -- 规定行驶时间
         ,shipment_stop.actual_driving_time                               -- 实际行驶时间
         ,shipment_stop.estimate_batch_time                               -- 应赶批次
         ,shipment_stop.actual_batch_time                                  -- 实赶批次时间
         ,shipment_stop.handling_type                                     -- 装卸类型
         ----------- 始发 -----------
         ,shipment_stop.start_site_sort_num                   -- 始发站点顺序编号
         ,shipment_stop.start_site_code                      -- 始发站点编号
         ,shipment_stop.start_site_name                      -- 始发站点名称
         ,shipment_stop.load_start_time                -- 开始装货
         ,shipment_stop.load_end_time                    -- 装货完成
         ,shipment_stop.lock_time                                         -- 锁车时间
         ,shipment_stop.app_departure_time                                -- APP离开时间
         ,shipment_stop.gps_departure_time                                -- GPS离开时间
         ,shipment_stop.weight_departure_time                             -- 过磅出站时间
         ,shipment_stop.planned_departure_time                            -- 计划离开时间
         ,shipment_stop.actual_departure_time                             -- 实际离开时间
         ,shipment_stop.departure_gross_weight                            -- 离开毛重
         ,shipment_stop.departure_tare_weight                             -- 离开皮重
         ,shipment_stop.departure_net_weight                              -- 离开净重
         ,shipment_stop.gps_depart_mileage                                -- GPS发车里程
         ----------- 目的 -----------
         ,shipment_stop.end_site_sort_num                     -- 目的站点顺序编号
         ,shipment_stop.end_site_code                      -- 目的站点编号
         ,shipment_stop.end_site_name                      -- 目的站点名称
        ,shipment_stop.unload_start_time                                 -- 开始卸货
        ,shipment_stop.unload_end_time                                   -- 卸货完成
        ,shipment_stop.unlock_time                                          -- 解锁时间
        ,unloading_arrival.unload_scan_time                                     -- 卸车时间
        ,unloading_arrival.unload_pre_station_code                             -- 卸车上一站编码
        ,unloading_arrival.unload_pre_station as unload_pre_station_name      -- 卸车上一站名称
        ,other_arrival.scan_time as arrival_car_scan_time                      -- 到解车时间
        ,other_arrival.car_code as arrival_car_code                            -- 到解车号
        ,shipment_stop.app_arrival_time                                  -- APP到达时间
        ,shipment_stop.gps_arrival_time                                  -- GPS到达时间
        ,shipment_stop.weight_arrival_time                               -- 过磅进站时间
        ,shipment_stop.planned_arrival_time                              -- 计划到达时间
        ,shipment_stop.actual_arrival_time                               -- 实际到达时间
        ,shipment_stop.arrival_gross_weight                              -- 到达毛重
        ,shipment_stop.arrival_tare_weight                               -- 到达皮重
        ,shipment_stop.arrival_net_weight                                -- 到达净重
        ,shipment_stop.gps_arrival_mileage                               -- GPS到车里程
        ,unloading_arrival.arrival_scan_time                                     -- 到件件时间
        ,unloading_arrival.arrival_pre_station_code                             -- 到件件上一站编码
        ,unloading_arrival.arrival_pre_station as arrival_pre_station_name     -- 到件件上一站名称
        ,shipment_stop.latest_arrival_time                               -- 最晚到车时间
        ,shipment_stop.estimate_arrival_time                             -- 预计到达时间=上一站实际发车时间+本站运输耗时(MIN)
    ---------经停-----------
        ,shipment_stop.plate_number    --    车牌号
        ,shipment_stop.vehicletype_id    --    车型ID
        ,shipment_stop.vehicletype_name    --    车型名称
        ,shipment_stop.trailer_number    --    挂车号
        ,shipment_stop.carrier_id    --    承运商ID
        ,shipment_stop.carrier_type    --    承运商类型
        ,shipment_stop.carrier_name    --    承运商全名
        ,shipment_stop.driver_name    --    司机名称
        ,shipment_stop.driver_contact    --    随车电话
        ,shipment_stop.quotation_model    --    报价模式
        ,shipment_stop.billing_way    --    计费方式（1：整车计重、2：零担计重、3：按趟计费）
        ,shipment_stop.settlement_vehicle_type_id    --    结算车型ID
        ,shipment_stop.settlement_vehicle_type_name    --    结算车型名称
        ,shipment_stop.freight_generate_status    --    运费流水生成状态：1
        ,shipment_stop.overtime_type    --    加班类型：11爆仓加班、12顶班、13应急加班
        ,shipment_stop.shipment_state    --    任务状态:待调度0
        ,shipment_stop.is_ballast    --    是否空放:1是
        ,shipment_stop.is_trunk_branch
        ,shipment_stop.load_weight
        ,shipment_stop.vehicle_volume
        ,shipment_stop.nuclear_load_num
        ,shipment_stop.is_stop    --是否经停
        ,shipment_stop.is_assist_line    --是否辅线
        ,shipment_stop.vehicleline_type    --线路类型 1总部招标；2加盟商供车；3临时车线；4网点自跑
        ,shipment_stop.has_out -- 是否兜底1-是2-否
        ,concat(substr(shipment_stop.shipno_planned_departure_time,1,10),'-',substr(shipment_stop.shipno_planned_departure_time,12,2)) as dt
    from tab_tms_branch_shipment_stop shipment_stop
    left join other_arrival other_arrival
    on shipment_stop.shipment_no = other_arrival.shipment_no
    and shipment_stop.end_site_code = other_arrival.scan_site_code
    left join tab_unloading_arrival unloading_arrival
    on shipment_stop.shipment_no = unloading_arrival.shipment_no
    and shipment_stop.end_site_code = unloading_arrival.scan_site_code
    -- where other_arrival.id = 1
),
-- 关联合并开始站点（装车，发件，封发）与结束站点（卸车，到件，到解）数据
road_site as (
    select
        nvl(start_site.shipment_id,end_site.shipment_id) as shipment_id
        ,nvl(start_site.shipment_no,end_site.shipment_no) as shipment_no
        ,nvl(start_site.shipment_name,end_site.shipment_name) as shipment_name
        ,nvl(start_site.waybill_no,end_site.waybill_no) as waybill_no
        ,nvl(start_site.package_code,end_site.package_code) as package_code
        ,nvl(start_site.start_code,end_site.start_code) as start_code
        ,nvl(start_site.start_name,end_site.start_name) as start_name
        ,nvl(start_site.end_code,end_site.end_code) as end_code
        ,nvl(start_site.end_name,end_site.end_name) as end_name
        ,nvl(start_site.shipment_site_cnt,end_site.shipment_site_cnt) as shipment_site_cnt
        ,nvl(start_site.vehicleline_id,end_site.vehicleline_id) as vehicleline_id
        ,nvl(start_site.vehicleline_code,end_site.vehicleline_code) as vehicleline_code
        ,nvl(start_site.vehicleline_name,end_site.vehicleline_name) as vehicleline_name
        ,nvl(start_site.shifts,end_site.shifts) as shifts
        ,nvl(start_site.business_attribute,end_site.business_attribute) as business_attribute
        ,nvl(start_site.operation_model,end_site.operation_model) as operation_model
        ,nvl(start_site.shipment_type,end_site.shipment_type) as shipment_type
        ,nvl(start_site.shipno_planned_departure_time,end_site.shipno_planned_departure_time) as shipno_planned_departure_time
        ,nvl(start_site.shipno_planned_arrival_time,end_site.shipno_planned_arrival_time) as shipno_planned_arrival_time
        ,nvl(start_site.shipno_actual_departure_time,end_site.shipno_actual_departure_time) as shipno_actual_departure_time
        ,nvl(start_site.shipno_actual_arrival_time,end_site.shipno_actual_arrival_time) as shipno_actual_arrival_time
        ,nvl(start_site.total_runtime,end_site.total_runtime) as total_runtime
        ,nvl(start_site.total_intransittime,end_site.total_intransittime) as total_intransittime
        ,nvl(start_site.span_days,end_site.span_days) as span_days
        ,nvl(start_site.standard_speed,end_site.standard_speed) as standard_speed
        ,nvl(start_site.mileage,end_site.mileage) as mileage
        ---------经停-----------
        -----------路段-----------
        ,nvl(start_site.road_section_id,end_site.road_section_id) as road_section_id
        ,nvl(start_site.usetime,end_site.usetime) as usetime
        ,nvl(start_site.road_mileage,end_site.road_mileage) as road_mileage
        ,nvl(start_site.speed,end_site.speed) as speed
        ,nvl(start_site.batch_time,end_site.batch_time) as batch_time
        ,nvl(start_site.plan_driving_time,end_site.plan_driving_time) as plan_driving_time
        ,nvl(start_site.actual_driving_time,end_site.actual_driving_time) as actual_driving_time
        ,nvl(start_site.estimate_batch_time,end_site.estimate_batch_time) as estimate_batch_time
        ,nvl(start_site.actual_batch_time,end_site.actual_batch_time) as actual_batch_time
        ,nvl(start_site.handling_type,end_site.handling_type) as handling_type
        -----------始发-----------
        ,nvl(start_site.start_site_sort_num,end_site.start_site_sort_num) as start_site_sort_num
        ,nvl(start_site.start_site_code,end_site.start_site_code) as start_site_code
        ,nvl(start_site.start_site_name,end_site.start_site_name) as start_site_name
        ,nvl(start_site.load_start_time,end_site.load_start_time) as load_start_time
        ,nvl(start_site.load_end_time,end_site.load_end_time) as load_end_time
        ,nvl(start_site.lock_time,end_site.lock_time) as lock_time
        ,nvl(start_site.load_scan_time,null) as load_scan_time
        ,nvl(start_site.load_next_station_code,null) as load_next_station_code
        ,nvl(start_site.load_next_station_name,null) as load_next_station_name
        ,nvl(start_site.handling_type,null) as start_handling_type
        ,nvl(start_site.send_car_scan_time,null) as send_car_scan_time
        ,nvl(start_site.send_car_code,null) as send_car_code
        ,nvl(start_site.app_departure_time,end_site.app_departure_time) as app_departure_time
        ,nvl(start_site.gps_departure_time,end_site.gps_departure_time) as gps_departure_time
        ,nvl(start_site.weight_departure_time,end_site.weight_departure_time) as weight_departure_time
        ,nvl(start_site.planned_departure_time,end_site.planned_departure_time) as planned_departure_time
        ,nvl(start_site.actual_departure_time,end_site.actual_departure_time) as actual_departure_time
        ,nvl(start_site.departure_gross_weight,end_site.departure_gross_weight) as departure_gross_weight
        ,nvl(start_site.departure_tare_weight,end_site.departure_tare_weight) as departure_tare_weight
        ,nvl(start_site.departure_net_weight,end_site.departure_net_weight) as departure_net_weight
        ,nvl(start_site.gps_depart_mileage,end_site.gps_depart_mileage) as gps_depart_mileage
        ,nvl(start_site.send_scan_time,null) as send_scan_time
        ,nvl(start_site.send_next_station_code,null) as send_next_station_code
        ,nvl(start_site.send_next_station_name,null) as send_next_station_name
        -----------目的-----------
        ,nvl(end_site.end_site_sort_num,start_site.end_site_sort_num) as end_site_sort_num
        ,nvl(end_site.end_site_code,start_site.end_site_code) as end_site_code
        ,nvl(end_site.end_site_name,start_site.end_site_name) as end_site_name
        ,nvl(end_site.unload_start_time,start_site.unload_start_time) as unload_start_time
        ,nvl(end_site.unload_end_time,start_site.unload_end_time) as unload_end_time
        ,nvl(end_site.unlock_time,start_site.unlock_time) as unlock_time
        ,nvl(end_site.unload_scan_time,null) as unload_scan_time
        ,nvl(end_site.unload_pre_station_code,null) as unload_pre_station_code
        ,nvl(end_site.unload_pre_station_name,null) as unload_pre_station_name
        ,nvl(end_site.handling_type,null) as end_handling_type
        ,nvl(end_site.arrival_car_scan_time,null) as arrival_car_scan_time
        ,nvl(end_site.arrival_car_code,null) as arrival_car_code
        ,nvl(end_site.app_arrival_time,start_site.app_arrival_time) as app_arrival_time
        ,nvl(end_site.gps_arrival_time,start_site.gps_arrival_time) as gps_arrival_time
        ,nvl(end_site.weight_arrival_time,start_site.weight_arrival_time) as weight_arrival_time
        ,nvl(end_site.planned_arrival_time,start_site.planned_arrival_time) as planned_arrival_time
        ,nvl(end_site.actual_arrival_time,start_site.actual_arrival_time) as actual_arrival_time
        ,nvl(end_site.arrival_gross_weight,start_site.arrival_gross_weight) as arrival_gross_weight
        ,nvl(end_site.arrival_tare_weight,start_site.arrival_tare_weight) as arrival_tare_weight
        ,nvl(end_site.arrival_net_weight,start_site.arrival_net_weight) as arrival_net_weight
        ,nvl(end_site.gps_arrival_mileage,start_site.gps_arrival_mileage) as gps_arrival_mileage
        ,nvl(end_site.arrival_scan_time,null) as arrival_scan_time
        ,nvl(end_site.arrival_pre_station_code,null) as arrival_pre_station_code
        ,nvl(end_site.arrival_pre_station_name,null) as arrival_pre_station_name
        ,nvl(end_site.latest_arrival_time,start_site.latest_arrival_time) as latest_arrival_time
        ,nvl(end_site.estimate_arrival_time,start_site.estimate_arrival_time) as estimate_arrival_time
        ---------经停-----------
        ,nvl(start_site.plate_number,end_site.plate_number) as plate_number
        ,nvl(start_site.vehicletype_id,end_site.vehicletype_id) as vehicletype_id
        ,nvl(start_site.vehicletype_name,end_site.vehicletype_name) as vehicletype_name
        ,nvl(start_site.trailer_number,end_site.trailer_number) as trailer_number
        ,nvl(start_site.carrier_id,end_site.carrier_id) as carrier_id
        ,nvl(start_site.carrier_type,end_site.carrier_type) as carrier_type
        ,nvl(start_site.carrier_name,end_site.carrier_name) as carrier_name
        ,nvl(start_site.driver_name,end_site.driver_name) as driver_name
        ,nvl(start_site.driver_contact,end_site.driver_contact) as driver_contact
        ,nvl(start_site.quotation_model,end_site.quotation_model) as quotation_model
        ,nvl(start_site.billing_way,end_site.billing_way) as billing_way
        ,nvl(start_site.settlement_vehicle_type_id,end_site.settlement_vehicle_type_id) as settlement_vehicle_type_id
        ,nvl(start_site.settlement_vehicle_type_name,end_site.settlement_vehicle_type_name) as settlement_vehicle_type_name
        ,nvl(start_site.freight_generate_status,end_site.freight_generate_status) as freight_generate_status
        ,nvl(start_site.overtime_type,end_site.overtime_type) as overtime_type
        ,nvl(start_site.shipment_state,end_site.shipment_state) as shipment_state
        ,nvl(start_site.is_ballast,end_site.is_ballast) as is_ballast
        ,nvl(start_site.is_trunk_branch,end_site.is_trunk_branch) as is_trunk_branch
        ,nvl(start_site.load_weight,end_site.load_weight) as load_weight
        ,nvl(start_site.vehicle_volume,end_site.vehicle_volume) as vehicle_volume
        ,nvl(start_site.nuclear_load_num,end_site.nuclear_load_num) as nuclear_load_num
        ,nvl(start_site.is_stop,end_site.is_stop) as is_stop
        ,nvl(start_site.is_assist_line,end_site.is_assist_line) as is_assist_line
        ,nvl(start_site.vehicleline_type,end_site.vehicleline_type) as vehicleline_type
        ,nvl(start_site.has_out,end_site.has_out) as has_out
        ,nvl(start_site.dt,end_site.dt) as dt
    from start_site start_site
    full join end_site end_site
    on start_site.dt = end_site.dt
    and start_site.shipment_no = end_site.shipment_no
    and start_site.start_site_code = end_site.start_site_code
    and start_site.end_site_code = end_site.end_site_code
    and start_site.waybill_no = end_site.waybill_no
)
-- 将运力数据与运单表关联，获得重量字段
insert overwrite table bidefault.dwd_wide_tms_road_waybill_hf partition(dt)
 select
    road_site.shipment_id
    ,road_site.shipment_no
    ,road_site.shipment_name
    ,road_site.waybill_no
    ,road_site.package_code
    ,null package_inside_charge_weight
    ,null package_total_volume
    ,null package_total_weight
    ,null waybill_weight
    ,dwd_order.terminal_dispatch_code
    ,road_site.start_code
    ,road_site.start_name
    ,road_site.end_code
    ,road_site.end_name
    ,road_site.shipment_site_cnt
    ,road_site.vehicleline_id
    ,road_site.vehicleline_code
    ,road_site.vehicleline_name
    ,road_site.shifts
    ,road_site.business_attribute
    ,road_site.operation_model
    ,road_site.shipment_type
    ,road_site.shipno_planned_departure_time
    ,road_site.shipno_planned_arrival_time
    ,road_site.shipno_actual_departure_time
    ,road_site.shipno_actual_arrival_time
    ,road_site.total_runtime
    ,road_site.total_intransittime
    ,road_site.span_days
    ,road_site.standard_speed
    ,road_site.mileage
    ,road_site.road_section_id
    ,road_site.usetime
    ,road_site.road_mileage
    ,road_site.speed
    ,road_site.batch_time
    ,road_site.plan_driving_time
    ,road_site.actual_driving_time
    ,road_site.estimate_batch_time
    ,road_site.actual_batch_time
    ,road_site.start_site_sort_num
    ,road_site.start_site_code
    ,road_site.start_site_name
    ,road_site.load_start_time
    ,road_site.load_end_time
    ,road_site.lock_time
    ,road_site.load_scan_time
    ,road_site.load_next_station_code as load_next_site_code
    ,road_site.load_next_station_name as load_next_site_name
    ,road_site.start_handling_type
    ,road_site.send_car_scan_time
    ,road_site.send_car_code
    ,road_site.app_departure_time
    ,road_site.gps_departure_time
    ,road_site.weight_departure_time
    ,road_site.planned_departure_time
    ,road_site.actual_departure_time
    ,road_site.departure_gross_weight
    ,road_site.departure_tare_weight
    ,road_site.departure_net_weight
    ,road_site.gps_depart_mileage
    ,road_site.send_scan_time
    ,road_site.send_next_station_code as send_next_site_code
    ,road_site.send_next_station_name as send_next_site_name
    ,road_site.end_site_sort_num
    ,road_site.end_site_code
    ,road_site.end_site_name
    ,road_site.unload_start_time
    ,road_site.unload_end_time
    ,road_site.unlock_time
    ,road_site.unload_scan_time
    ,road_site.unload_pre_station_code as unload_pre_site_code
    ,road_site.unload_pre_station_name as unload_pre_site_name
    ,road_site.end_handling_type
    ,road_site.arrival_car_scan_time
    ,road_site.arrival_car_code
    ,road_site.app_arrival_time
    ,road_site.gps_arrival_time
    ,road_site.weight_arrival_time
    ,road_site.planned_arrival_time
    ,road_site.actual_arrival_time
    ,road_site.arrival_gross_weight
    ,road_site.arrival_tare_weight
    ,road_site.arrival_net_weight
    ,road_site.gps_arrival_mileage
    ,road_site.arrival_scan_time
    ,road_site.arrival_pre_station_code as arrival_pre_site_code
    ,road_site.arrival_pre_station_name as arrival_pre_site_name
    ,road_site.latest_arrival_time
    ,road_site.estimate_arrival_time
    ,road_site.plate_number
    ,road_site.vehicletype_id
    ,road_site.vehicletype_name
    ,road_site.trailer_number
    ,road_site.carrier_id
    ,road_site.carrier_type
    ,road_site.carrier_name
    ,road_site.driver_name
    ,road_site.driver_contact
    ,road_site.load_weight
    ,road_site.vehicle_volume
    ,road_site.nuclear_load_num
    ,road_site.quotation_model
    ,road_site.billing_way
    ,road_site.settlement_vehicle_type_id
    ,road_site.settlement_vehicle_type_name
    ,road_site.freight_generate_status
    ,road_site.overtime_type
    ,road_site.shipment_state
    ,road_site.is_ballast
    ,road_site.is_trunk_branch
    ,road_site.is_stop
    ,road_site.is_assist_line
    ,road_site.vehicleline_type
    ,road_site.has_out
    ,dwd_order.real_pick_network_code
    ,road_site.dt
 from road_site road_site
 left join (
     select waybill_id,terminal_dispatch_code,real_pick_network_code
     from jms_dwd.dwd_yl_oms_oms_order_hf
     where dt >= concat('{{ execution_date | date_add(-30) | cst_ds }}','-00')
     and dt <= '{{ execution_date | hour_add(-1) | cst_hour }}'
     and waybill_rnk=1
 ) dwd_order
 on road_site.waybill_no = dwd_order.waybill_id
 distribute by dt , pmod(hash(waybill_no),20)
;
